package com.hdc.hdc.warehouseManage.utill;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import com.hdc.hdc.warehouseManage.entity.Inorder;
import com.hdc.hdc.warehouseManage.entity.InorderAndGoods;
import com.hdc.hdc.warehouseManage.exception.MyException;

/**
 * 解析excel文件
 * @author GJY
 * 人生格言：学以致用终将成功
 * @version 创建时间：2018年4月26日 上午10:45:33 
 *
 */
public class ParseExcelUtil {
	
	
	private static final Logger log = LoggerFactory.getLogger(ParseExcelUtil.class);


	/**
	 * 解析上传文件并返回inorderGoods类
	 * @param file
	 * @return
	 */
	public synchronized static List<InorderAndGoods> parseExcel(Inorder inorder, MultipartFile file) {
		
		String fileName = file.getOriginalFilename();
		
		log.info("当前上传文件："+fileName);
		//初始化输入流  
	    InputStream is = null;   
		try {
			String path = ResourceUtils.getURL("classpath:").getPath();
			
			//先读取零时文件
			File target = new File(path+"/templates/excelmodual",fileName);
			file.transferTo(target);
			
			log.info("文件保存成功....................................");
			
			 //根据新建的文件实例化输入流  
	        is = new FileInputStream(target); 
	        
	      //根据版本选择创建Workbook的方式  
	        Workbook wb = null;
	        
	      //根据文件名判断文件是2003版本还是2007版本  
			if (FileUtil.isExcel2007(fileName)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = new HSSFWorkbook(is);
			}
			
			return  readExcelValue(inorder,wb,target);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new MyException(e.getMessage());
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					is = null;
					e.printStackTrace();
				}
			}
		} 
	}

	/*
	 * 读取excel
	 */
	private static List<InorderAndGoods> readExcelValue(Inorder inorder,Workbook wb, File tempFile) {
		
		List<InorderAndGoods> goods = new ArrayList<InorderAndGoods>(200);
		
		//得到第一个shell    
	    Sheet sheet=wb.getSheetAt(0);
	    
	    //得到Excel的行数  
	    int totalRows=sheet.getPhysicalNumberOfRows();  
	    //总列数  
	    int totalCells = 0;  
	    
	    //得到Excel的列数(前提是有行数)，从第三行算起  
	    if(totalRows>2 && sheet.getRow(1) != null){  
	        totalCells=sheet.getRow(1).getPhysicalNumberOfCells();  
	    }  
	    
	    /*
	     * 数据校验
	     * 1、仓库必须一致
	     * 2、货位必须一致
	     * 3、库位必须一致
	     * 4、物资名字不能空
	     * 5、物资单位不能为空
	     * 6、物资单价不能为空
	     * 7、物资编号不能为空
	     */
	    
	    //仓库一致
	    Map<String,String> houseMap = new ConcurrentHashMap<String,String>(10);
	    Map<String,String> positionMap = new ConcurrentHashMap<String,String>(10);
	    Map<String,String> locationMap = new ConcurrentHashMap<String,String>(10);
	    
	    //从第三行开始
	    for (int r = 2; r < totalRows; r++) {
	    	
	    	InorderAndGoods good = new InorderAndGoods();
	    	
	    	good.setInordernum(inorder.getInorderNum());
	    	
	    	Row row = sheet.getRow(r);
	    	
			if(row == null) {
				throw new MyException("第"+(r+1)+"行数据有问题，存在空数据");
			}
			
			// 循环Excel行
			for (int s = 0; s < totalCells; s++) {
				
				Cell cell = row.getCell(s);
				
				switch (s) {
				case 0:
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK || !IsNotNullValit.StringIsNotNull(cell.getStringCellValue())) {
						
						throw new MyException(errorMessage("物资名称需要输入",r,s));
					}
					String goodsName = cell.getStringCellValue();
					good.setGoodsName(goodsName);
					
					continue;
				case 1:
					
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK || !IsNotNullValit.StringIsNotNull(cell.getStringCellValue())) {
						throw new MyException(errorMessage("商品编码不能为空",r,s));
					}
					String goodsSku = cell.getStringCellValue();
					good.setGoodsSku(goodsSku);
					
					continue;
				case 2:
					
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						throw new MyException(errorMessage("商品价格不能为空",r,s));
					}
					
					if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						Double price = cell.getNumericCellValue();
						good.setGoodsPrice(price);
					}else {
						String price = cell.getStringCellValue();
						good.setGoodsPrice(Double.valueOf(price));
					}
					
					continue;
				case 3:
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						throw new MyException(errorMessage("商品数量不能为空",r,s));
					}
					if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						Long num = (long) cell.getNumericCellValue();
						good.setGoodsNum(num);
					}else {
						String num = cell.getStringCellValue();
						good.setGoodsNum(Long.valueOf(num));
					}
					
					continue;
				case 4:
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						throw new MyException(errorMessage("商品单位不能为空",r,s));
					}
					good.setGoodsUnit(cell.getStringCellValue());
					
					continue;
				case 5:
					
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						log.info("用户:"+inorder.getCreator()+"没有填写生产日期信息");
					}else{
						
						try {
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date produceDate = cell.getDateCellValue();
								good.setGoodsProduceDate(produceDate);
							}
						} catch (Exception e) {
							e.printStackTrace();
							log.info("不是日期类型");
							try {
								String produceDate = cell.getStringCellValue();
								good.setGoodsProduceDate(DateUtil.getCurrentDate(produceDate, "yyyyMMdd HHmm"));
								
							} catch (Exception e2) {
								throw new MyException(e2.getMessage());
							}
						}
						
						
					}
					continue;
				case 6:
					
					if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						log.info("用户:"+inorder.getCreator()+"没有填写保质期信息");
					}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
						Integer goodsPeriod = (int)cell.getNumericCellValue();
						good.setGoodsPeriod(goodsPeriod);
					}else {
						String goodsPeriod = cell.getStringCellValue();
						good.setGoodsPeriod(Integer.valueOf(goodsPeriod));
					}
					
					continue;
				case 7:
					
					if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						log.info("用户:"+inorder.getCreator()+"没有填写供应商信息");
					}else {
						good.setSupplierName(cell.getStringCellValue());
					}
					
					continue;
				case 8:
					
					if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						throw new MyException(errorMessage("仓库位置需要填写",r,s));
					}
					
					String houseName = cell.getStringCellValue();
					
					if (!IsNotNullValit.StringIsNotNull(houseName)) {
						throw new MyException(errorMessage("仓库位置需要填写",r,s));
					}
					
					if (houseMap.size() == 0) {
						houseMap.put(houseName, houseName);
					}else {
						String mapVal = houseMap.get(houseName);
						//map有数据但是又没有现在这个仓库名字的数据那就说明仓库不一致
						if (!houseName.equals(mapVal)) {
							throw new MyException(errorMessage("一张单只能对应一个仓库，如需多个仓库请分开单导入或后续做仓库转移单",r,s));
						}
					}
					
					inorder.setToWhereHouseName(houseName);
					
					continue;
				case 9:
					
					if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						throw new MyException(errorMessage("仓库库位需要填写",r,s));
					}
					
					String positionName = cell.getStringCellValue();
					
					if (!IsNotNullValit.StringIsNotNull(positionName)) {
						throw new MyException(errorMessage("仓库库位需要填写",r,s));
					}
					
					if (positionMap.size() == 0) {
						positionMap.put(positionName, positionName);
					}else {
						String mapVal = positionMap.get(positionName);
						//map有数据但是又没有现在这个仓库名字的数据那就说明仓库不一致
						if (!positionName.equals(mapVal)) {
							throw new MyException(errorMessage("一张单只能对应一个库位，如需多个库位请分开单导入或后续做仓库转移单",r,s));
						}
					}
					inorder.setToWherePositionName(positionName);
					continue;
				case 10:
					
					String locationName = cell.getStringCellValue();
					
					if (!IsNotNullValit.StringIsNotNull(locationName)) {
						continue;
					}
					
					if (locationMap.size() == 0) {
						locationMap.put(locationName, locationName);
					}else {
						String mapVal = locationMap.get(locationName);
						//map有数据但是又没有现在这个仓库名字的数据那就说明仓库不一致
						if (!locationName.equals(mapVal)) {
							throw new MyException(errorMessage("一张单只能对应一个货位，如需多个货位请后续修改物资",r,s));
						}
					}
					
					inorder.setToWhereLocationName(locationName);
					continue;

				default:
					continue;
				}
				
			}
			goods.add(good);
		}
		
		return goods;
	}

	
	private static String errorMessage(String msg, int r, int s) {

		StringBuffer sb = new StringBuffer(msg);
		sb.append("!出错行数:").append((r + 1)).append("行!").append("第").append((s + 1)).append("格");

		return sb.toString();

	}
	
}
